/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.lb.trac.service;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.TreeSet;

import org.hibernate.criterion.Criterion;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Restrictions;
import org.hibernate.transform.ResultTransformer;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.orm.hibernate3.HibernateTemplate;

import com.lb.trac.dao.DAOService;
import com.lb.trac.pojo.Evento;
import com.lb.trac.pojo.Profilo;
import com.lb.trac.pojo.Progetti;
import com.lb.trac.pojo.Societa;
import com.lb.trac.pojo.Stati;
import com.lb.trac.pojo.StoricoTicket;
import com.lb.trac.pojo.Tickets;
import com.lb.trac.pojo.Utenti;
import com.lb.trac.pojo.condition.FindProfiloDyDescrizione;
import com.lb.trac.pojo.condition.FindSocietaByCodClienteHRPO;
import com.lb.trac.pojo.condition.FindUtenteByUsername;
import com.lb.trac.pojo.condition.order.OrderCondition;
import com.lb.trac.util.SQLCondition;
import com.lb.trac.util.TracSetupUtil;

/**
 * 
 * @author edoardo
 */
public class SearchService {

	private HibernateTemplate hibernateTemplate;
	private DAOService daoService;
	private TracSetupUtil tracSetupUtil;
	private JdbcTemplate jdbcTemplateHrpo;
	private JdbcTemplate jdbcTemplate;


	public Tickets[] findTicketsByStato(Tickets tickets) {
		DetachedCriteria criteria = DetachedCriteria.forClass(Tickets.class);

		// List<Tickets> ticketList =
		// hibernateTemplate.findByCriteria(criteria.add(Restrictions.eq("stati",
		// tickets.getStati())));
		// Tickets[] ticketsArray = ticketList.toArray(new Tickets[0]);
		Tickets[] ticketsArray = daoService.findByCriteria(new Tickets[0],
				Restrictions.eq("stati", tickets.getStati()),
				Tickets.class);
		return ticketsArray;
	}

	public Profilo findProfiloByDescrizione(String descrizione) {
		Criterion c = Restrictions.eq("descrizione", descrizione);
		Profilo[] p = daoService.findByCriteria(new Profilo[0], c,
				Profilo.class);
		return p[0];
	}

	public Utenti[] findUtentiAbilitatiProgettoByProfilo(String idProgetto,
			Profilo profilo) {
		Criterion cProgetto = Restrictions.eq("idProgetto", new BigDecimal(
				idProgetto));

		Progetti[] p = daoService.findByCriteria(new Progetti[0], cProgetto,
				Progetti.class);

		Utenti[] u = p[0].getUtentis().toArray(new Utenti[0]);
		return u;
	}

	public Utenti findUtenteById(String idUtente) {
		Utenti u = daoService.findByPK(Utenti.class, new BigDecimal(idUtente));

		return u;
	}

	public Utenti findUtente(String username, String md5) {
		Criterion usernameCriterion = Restrictions.eq("username", username);
		Criterion passwordCriterion = Restrictions.eq("password", md5);
		Utenti[] u = daoService.findByCriteria(new Utenti[0],
				Restrictions.and(usernameCriterion, passwordCriterion),
				Utenti.class);
		// non faccio controlli se trovo o no record dato che viene fatto già
		// nel validator del login
		return u[0];
	}

	public Utenti insertOrFindUtenteHRPO(String username, String codCliente) {

		Utenti[] utenti = find(new Utenti[0],
				new FindUtenteByUsername(username.toUpperCase()), Utenti.class);
		boolean insert = !(utenti == null || utenti.length != 0);
		Utenti u = null;
		if (insert) {
			u = new Utenti();
			List<Map<String, Object>> utentis = getJdbcTemplateHrpo()
					.queryForList(
							"select * from TAPE0wPROFILO_UTENTI where cod_cliente=? and login_utente=?",
							codCliente, username.toUpperCase());
			Map<String, Object> mapUtente = utentis.get(0);
			username = (String) mapUtente.get("LOGIN_UTENTE");
			codCliente = (String) mapUtente.get("COD_CLIENTE");
			String nominativoCompleto = (String) mapUtente
					.get("NOMINATIVO_COMPLETO");

			int idxBlank = nominativoCompleto.indexOf(" ");
			String cognome = nominativoCompleto.substring(0, idxBlank);
			String nome = nominativoCompleto.substring(idxBlank + 1).trim();
			u.setNome(nome);
			u.setPassword((String) mapUtente.get("PASSWORD"));
			u.setCognome(cognome);
			u.setUsername(username.toUpperCase());
			u.setAbilitato(true);
			Societa s = find(new Societa[0], new FindSocietaByCodClienteHRPO(
					codCliente), Societa.class)[0];
			Profilo p = find(new Profilo[0], new FindProfiloDyDescrizione(
					"USER"), Profilo.class)[0];
			Progetti progetti = s.getProgettis().toArray(new Progetti[0])[0];

			u.setSocieta(s);

			u.setProfilo(p);
			u.setEmail((String) mapUtente.get("EMAIL"));

			Set setProgettis = new TreeSet<Progetti>();
			setProgettis.add(progetti);
			u.setProgettis(setProgettis);

			daoService.insert(u);
		} else {
			u = utenti[0];
		}
		

		// Utenti[] u = daoService.findByCriteria(new Utenti[0],
		// usernameCriterion, Utenti.class);
		// if (u!=null && u.length==1) {
		// result.setUtenti(tracSetupUtil.copyPojoProperties(u[0]));
		// }
		return u;
	}

	// public UtenteProgettoView[] findProgettiByUtente(Utenti u){
	//
	// }

	public Utenti findUtenteByUsername(String username)
			throws UserNotFoundException {
		Utenti utente = new Utenti();
		Utenti[] u = daoService.findByCriteria(new Utenti[0],
				Restrictions.eq("username", username), Utenti.class);
		if (u == null || u.length == 0) {
			throw new UserNotFoundException("Utente inesistente");
		}
		return u[0];
	}

	public HibernateTemplate getHibernateTemplate() {
		return hibernateTemplate;
	}

	public void setHibernateTemplate(HibernateTemplate hibernateTemplate) {
		this.hibernateTemplate = hibernateTemplate;
	}

	public DAOService getDaoService() {
		return daoService;
	}

	public void setDaoService(DAOService daoService) {
		this.daoService = daoService;
	}

	public TracSetupUtil getTracSetupUtil() {
		return tracSetupUtil;
	}

	public void setTracSetupUtil(TracSetupUtil tracSetupUtil) {
		this.tracSetupUtil = tracSetupUtil;
	}

	/**
	 * Prende l'elenco completo dei tickets senza filtrare. Usato da utente
	 * amministratore
	 * 
	 * @return
	 */
	public Tickets[] findTickets() {
		Tickets[] t = daoService.findAll(new Tickets[0], Tickets.class, null);
		return t;
	}

	public Tickets[] findTickets(int firstResult) {
		Tickets[] t = daoService.findAll(new Tickets[0], Tickets.class,
				firstResult, null);
		return t;
	}

	public <T> T[] find(T[] t, SQLCondition sqlCondition, Class<T> aClass,
			OrderCondition orderCondition) {
		if (sqlCondition != null) {
			t = daoService.findByCriteria(t, sqlCondition.create(), aClass,
					orderCondition);
		} else {
			t = daoService.findAll(t, aClass, orderCondition);
		}
		// for (int i = 0; i < t.length; i++) {
		//
		// t[i] =
		// TracSetupUtil.copyProperties(t[i],BeanUtils.instantiate(aClass));
		// }
		return t;
	}

	public <T> T[] find(T[] t, SQLCondition sqlCondition, Class<T> aClass) {
		return find(t, sqlCondition, aClass, true);
	}

	public <T> T[] find(T[] t, SQLCondition sqlCondition, Class<T> aClass,
			boolean dissocia) {
		if (sqlCondition != null) {
			t = daoService.findByCriteria(t, sqlCondition.create(), aClass,
					null);
		} else {
			t = daoService.findAll(t, aClass, null);
		}
		// if (dissocia) {
		// for (int i = 0; i < t.length; i++) {
		//
		// t[i]
		// =TracSetupUtil.copyProperties(t[i],BeanUtils.instantiate(aClass));
		// }
		// }
		return t;
	}

	public <T> T[] find(T[] t, Class<T> aClass) {
		return find(t, null, aClass);
	}

	public <T> T[] find(T[] t, String queryString,
			final ResultTransformer transformer, Object... paramsValues) {

		return daoService.find(t, queryString, transformer, paramsValues);
	}

	/**
	 * trova tutti i ticket che soddisfano la lista di parametri in input
	 * 
	 * @param params
	 * @return
	 */
	public Tickets[] findTickets(SQLCondition cc) {
		Criterion criterion = cc.create();
		Tickets[] t = daoService.findByCriteria(new Tickets[0], criterion,
				Tickets.class);
		return t;
	}

	public Object count(Class<?> aClass, SQLCondition cc) {
		return daoService.count((cc == null ? null : cc.create()), aClass);
	}

	public Object count(Class<?> aClass) {
		return count(aClass, null);
	}

	public <T> T[] find(T[] t, SQLCondition cc, int firstResult,
			Class<T> aClass, OrderCondition orderCondition) {
		return find(t,cc,firstResult,0,aClass,orderCondition);
	}
	
	public <T> T[] find(T[] t, SQLCondition cc, int firstResult,int maxResult,
			Class<T> aClass, OrderCondition orderCondition) {
		Criterion criterion = null;
		if (cc != null) {
			criterion = cc.create();
		}
		T[] result = null;
		if (orderCondition != null) {
			result = daoService.findByCriteria(t, criterion, aClass,
					firstResult,maxResult, orderCondition);
		} else {
			result = daoService.findByCriteria(t, criterion, aClass,
					firstResult,maxResult);
		}
		// for (int i = 0; i < result.length; i++) {
		// T source = result[i];
		//
		// source =
		// TracSetupUtil.copyProperties(t[i],BeanUtils.instantiate(aClass));
		// result[i] = source;
		// }
		return result;
	}

	public <T> T[] find(T[] t, SQLCondition cc, int firstResult, Class<T> aClass) {
		return find(t, cc, firstResult, aClass, null);
	}

	public Tickets[] findTickets(SQLCondition cc, int firstResult) {
		Criterion criterion = cc.create();
		Tickets[] t = daoService.findByCriteria(new Tickets[0], criterion,
				Tickets.class, firstResult);
		return t;
	}

	public Tickets[] findTickets(SQLCondition cc, int firstResult,
			OrderCondition orderCondition) {
		Criterion criterion = cc.create();
		Tickets[] t = daoService.findByCriteria(new Tickets[0], criterion,
				Tickets.class, firstResult, orderCondition);
		return t;
	}

	public Progetti findProgettoByTicket(String idTicket) {
		Tickets t = new Tickets();
		t.setIdTicket(new BigDecimal(idTicket));
		Criterion cr = Restrictions.eq("idTicket", new BigDecimal(idTicket));
		Tickets[] tickets = daoService.findByCriteria(new Tickets[0], cr,
				Tickets.class);
		if (tickets != null && tickets.length > 0) {
			Progetti progetto = new Progetti();
			progetto.setIdProgetto(tickets[0].getIdProgetto());
			cr = Restrictions.eq("idProgetto", progetto.getIdProgetto());
			Progetti[] p = daoService.findByCriteria(new Progetti[0], cr,
					Progetti.class);
			if (p != null && p.length > 0) {
				return p[0];
			}
		}
		return null;
	}

	public Stati[] findAllStati() {
		Stati[] s = daoService.findAll(new Stati[0], Stati.class, null);
		return s;
	}

	public Evento[] findEventiDaInviare() {
		Criterion c = Restrictions.eq("inviato", "0");
		Evento[] result = daoService.findByCriteria(new Evento[0], c,
				Evento.class);
		for (int i = 0; i < result.length; i++) {
			Evento evento = result[i];
			result[i] = tracSetupUtil.copyPojoProperties(evento);
		}
		return result;

	}

	public StoricoTicket[] findMaxStoricoTicketByTicket(StoricoTicket[] t,
			Tickets ticket) {
		StoricoTicket[] st = getDaoService().findByNamedQuery(t,
				"GET_LAST_STORICO", ticket.getIdTicket());
		// for (int i = 0; i < st.length; i++) {
		// StoricoTicket storicoTicket = st[i];
		// st[i]=TracSetupUtil.copyProperties(storicoTicket, new
		// StoricoTicket(), PropertiesNotDuplicatable.STORICO_TICKET);
		// }
		return st;
	}

	public Progetti findProgettoById(String idProgetto) {
		Criterion c = Restrictions.eq("idProgetto", new BigDecimal(idProgetto));
		Progetti[] result = daoService.findByCriteria(new Progetti[0], c,
				Progetti.class);
		return result[0];
	}

	public <T> T[] find(T[] t, int firstRecord, Class<T> aClass) {
		return find(t, null, firstRecord, aClass, null);
	}

	public int getMaxResults() {
		return getDaoService().getMaxResults();
	}

	public JdbcTemplate getJdbcTemplateHrpo() {
		return jdbcTemplateHrpo;
	}

	public void setJdbcTemplateHrpo(JdbcTemplate jdbcTemplateHrpo) {
		this.jdbcTemplateHrpo = jdbcTemplateHrpo;
	}

	public Utenti findUtenteByUsernameAndCodCliente(String username,
			String codCliente) throws UserNotFoundException {
		DetachedCriteria dc = DetachedCriteria.forClass(Utenti.class);
		dc = dc.add(Restrictions.eq("username", username.toUpperCase()));
		dc = dc.createCriteria("societa").add(
				Restrictions.eq("descBreve", codCliente));

		List us = getHibernateTemplate().findByCriteria(dc);
		Utenti[] u = (Utenti[]) us.toArray(new Utenti[0]);
		if (u == null || u.length == 0) {
			throw new UserNotFoundException("Utente inesistente");
		}
		return u[0];
	}

	public Progetti[] findProgettiNonAssociatiAdUtente(String idUtente) {
		String sql = "select ID_PROGETTO,DESCRIZIONE from progetti p where p.id_progetto not in(select id_progetto from utente_progetto up, utenti u where u.id_utente=up.id_utente and u.id_utente=? )";
		Utenti u = findUtenteById(idUtente);
		List<Map<String, Object>> progettis = getJdbcTemplate().queryForList(
				sql, new BigDecimal(idUtente));
		Iterator<Map<String, Object>> rows = progettis.iterator();
		List<Progetti> results = new ArrayList<Progetti>();
		while (rows.hasNext()) {
			Map<java.lang.String, java.lang.Object> map = (Map<java.lang.String, java.lang.Object>) rows
					.next();
			Progetti p = findProgettoById(map.get("ID_PROGETTO").toString());
			boolean add = true;
			if (u.getProfilo().getDescrizione().equals("USER") && (u.getSocieta().getHrpo()!=null && u.getSocieta().getHrpo().booleanValue()))  {
				add = p.getSocieta().equals(u.getSocieta());
			}
			if (add) {
				results.add(p);
			}

		}
		return results.toArray(new Progetti[0]);
	}

	public JdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	public <T> T[] find(T[] t, int firstResult, int limit,
			Class<T> c) {
		return find(t,null,firstResult,limit,c,null);
	}

}
